﻿Public Class quickBooksForm

    Dim magentoTable As New DataTable
    Dim quickbooksTable As New DataTable
    Dim skippedTable As New DataTable
    Dim lostMagentoSKU As New DataTable 'SKU that are in Magetno- but are not paired in Quickbooks-SKU changed or deleted on Quickbooks
    Dim newItemsFromQuickBooks As New DataTable ' SKU does not exist on current Magento data- so its a newly added item
    Dim convertedNewItemsFromQuickBooks As DataTable
    Dim updatedTable As New DataTable 'Found a match and updated prices and quantities

    Dim progCount As Integer


 

    Private Sub loadQB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles loadQB.Click

        If CheckBoxXML.Checked Then
            'Import the Magento Exported Excel Style using my class. (Not recommended)
            Dim loader As New xml_loader()
            loader.loadTable()
            magentoTable = loader.GET_TABLE
            MagentoDBDataGridView.DataSource = magentoTable

        Else
            'Normal DataSet exported XML usign .NET XMLWritter

            Dim opn As New OpenFileDialog
            opn.Filter = "XML V1 (.NET)|*.xml"
            If opn.ShowDialog = Windows.Forms.DialogResult.OK Then
                Dim ds As New DataSet
                Try

                    ds.ReadXml(opn.FileName)
                Catch ex As Exception
                    MessageBox.Show("Loading standard XML error - " & ex.ToString)
                End Try

                MagentoDBDataGridView.DataSource = ds.Tables(0)
                magentoTable = ds.Tables(0)
            End If

        End If

       
        TabMagentoDB.Text = "Magento DB (" & magentoTable.Rows.Count.ToString & ")"


        Dim loaderQB As New Quickbooks
        quickbooksTable = loaderQB.getDataTable()
        QuickbooksDBDataGridView.DataSource = quickbooksTable
        TabQuickbooksDB.Text = "QuickbooksDB (" & quickbooksTable.Rows.Count.ToString & ")"

        'sort the SKU out- remove non inventory items
        removeUnwantedItems()

        updateMagentoItems()

        loadQB.Text = "Complete"

    End Sub


    Sub updateMagentoItems()
        loadQB.Text = "Please Wait..."
        loadQB.Update()

        Dim matches As Integer = 0
        Dim qbCnt As Integer = quickbooksTable.Rows.Count - 1
        Dim mgCnt As Integer = magentoTable.Rows.Count - 1

        newItemsFromQuickBooks = quickbooksTable.Copy 'remove matched items- leaves only new items behind
        lostMagentoSKU = magentoTable.Copy 'remove matched item - leaves lost items behind

        updatedTable = magentoTable.Clone 'create table structure
        'updatedTable.Clear() 'clear all items ready to insert udpated items

        Try
            Do While qbCnt >= 0  'place holder for qb line
                Do While mgCnt >= 0 'run thorugh magento file and find pairs - or not find pair is important
                    'MATCH FOUND- update fields
                    If quickbooksTable.Rows(qbCnt).Item("!INVITEM").ToString.Equals(magentoTable.Rows(mgCnt).Item("sku").ToString) Then
                        Dim newRow As DataRow = magentoTable.Rows(mgCnt)

                        newRow.Item("price") = quickbooksTable.Rows(qbCnt).Item("PRICE").ToString
                        newRow.Item("cost") = quickbooksTable.Rows(qbCnt).Item("COST").ToString

                        newRow.Item("qty") = quickbooksTable.Rows(qbCnt).Item("QNTY_IN_STOCK").ToString
                        If CInt(quickbooksTable.Rows(qbCnt).Item("QNTY_IN_STOCK").ToString) = 0 Then
                            If CheckBoxDisableZeroStock.Checked Then
                                newRow.Item("status") = "Disabled"
                                newRow.Item("is_in_stock") = "0"
                            End If
                        Else
                            newRow.Item("status") = "Enabled"
                            newRow.Item("is_in_stock") = "1"
                        End If
                        newRow.Item("visibility") = "Catalog, Search"


                        updatedTable.ImportRow(newRow)

                        newItemsFromQuickBooks.Rows(qbCnt).Delete()
                        'lostMagentoSKU.Rows(mgCnt).Delete()
                    End If
                    mgCnt -= 1
                Loop

                qbCnt -= 1
                mgCnt = magentoTable.Rows.Count - 1
                progCount += 1

                If progCount > 100 Then
                    loadQB.Text = "Pairing Data - %" & CStr(CInt((qbCnt / mgCnt) * 100))
                    loadQB.Update()
                    progCount = 0
                End If

            Loop

            lostMagentoSKU.AcceptChanges()

            'remove disabled items from magentotable- they will never be used anyway
            mgCnt = lostMagentoSKU.Rows.Count - 1
            Do While mgCnt > 0
                If lostMagentoSKU.Rows(mgCnt).Item("status").ToString.Equals("Disabled") Then
                    lostMagentoSKU.Rows(mgCnt).Delete()
                End If
                mgCnt -= 1

                progCount += 1
                If progCount > 100 Then
                    loadQB.Text = "Removing Disabled - " & CStr(mgCnt)
                    loadQB.Update()
                    progCount = 0
                End If
            Loop



        Catch ex As Exception
            MessageBox.Show("An error occured." & vbNewLine & "QBTableSize=" & quickbooksTable.Rows.Count & " QbCnt=" & qbCnt.ToString & "   MagTableSize=" & magentoTable.Rows.Count & " MgCnt=" & mgCnt.ToString & vbNewLine & "Make sure your Magento file has these columns (price,cost,qty,status,is_in_stock) Case Sensitive." & vbNewLine & "Make sure that the quickbooks has these coulumns (PRICE,COST,QNTY_IN_STOCK) The first QTY coulmn is converted into QTY_IN_STOCK. Case Sensitive" & vbNewLine & ex.ToString)
            Exit Sub
            Me.Close()

        End Try

       

        lostMagentoSKU.AcceptChanges()
        newItemsFromQuickBooks.AcceptChanges()
        updatedTable.AcceptChanges()

        'THis is for new items

        'remove ignored items from new table
        qbCnt = newItemsFromQuickBooks.Rows.Count - 1
        Do While qbCnt > 0

            For i As Integer = 0 To ListBoxIgnoreNewItems.Items.Count - 1
                If newItemsFromQuickBooks.Rows(qbCnt).Item("NAME").ToString.Contains(ListBoxIgnoreNewItems.Items(i).ToString) Or CInt(newItemsFromQuickBooks.Rows(qbCnt).Item("QNTY_IN_STOCK").ToString) = 0 Then
                    newItemsFromQuickBooks.Rows(qbCnt).Delete()
                    Exit For
                End If
            Next
            qbCnt -= 1

            progCount += 1
            If progCount > 100 Then
                loadQB.Text = "Removing Ignored - " & CStr(qbCnt)
                loadQB.Update()
                progCount = 0
            End If
        Loop

        newItemsFromQuickBooks.AcceptChanges()

        'convert new items from quickbooks columns to real magento columns ready to be exported
        Dim qbAttrib As New Quickbooks
        convertedNewItemsFromQuickBooks = magentoTable.Clone
        convertedNewItemsFromQuickBooks.Columns.Add("category_ids")
        convertedNewItemsFromQuickBooks.Columns.Add("attribute_set")
        If Not convertedNewItemsFromQuickBooks.Columns.Contains("short_description") Then convertedNewItemsFromQuickBooks.Columns.Add("short_description")
        If Not convertedNewItemsFromQuickBooks.Columns.Contains("description") Then convertedNewItemsFromQuickBooks.Columns.Add("description")
        If Not convertedNewItemsFromQuickBooks.Columns.Contains("weight") Then convertedNewItemsFromQuickBooks.Columns.Add("weight")

        If Not convertedNewItemsFromQuickBooks.Columns.Contains("type") Then convertedNewItemsFromQuickBooks.Columns.Add("type")
        If Not convertedNewItemsFromQuickBooks.Columns.Contains("visibility") Then convertedNewItemsFromQuickBooks.Columns.Add("visibility")
        If Not convertedNewItemsFromQuickBooks.Columns.Contains("tax_class_id") Then convertedNewItemsFromQuickBooks.Columns.Add("tax_class_id")
        If Not convertedNewItemsFromQuickBooks.Columns.Contains("name") Then convertedNewItemsFromQuickBooks.Columns.Add("name")

        'If convertedNewItemsFromQuickBooks.Columns.Contains("product_id") Then convertedNewItemsFromQuickBooks.Columns.Remove("product_id")



        qbCnt = 0
        Do While qbCnt < newItemsFromQuickBooks.Rows.Count
            Dim newRow As DataRow = convertedNewItemsFromQuickBooks.NewRow

            newRow.Item("sku") = newItemsFromQuickBooks(qbCnt).Item("!INVITEM")
            newRow.Item("type") = "simple"
            If newItemsFromQuickBooks(qbCnt).Item("CUSTFLD1").ToString.Equals("") Then
                newRow.Item("name") = "NONAME"
            Else
                newRow.Item("name") = newItemsFromQuickBooks(qbCnt).Item("CUSTFLD1")
            End If
            newRow.Item("price") = newItemsFromQuickBooks(qbCnt).Item("PRICE")
            newRow.Item("cost") = newItemsFromQuickBooks(qbCnt).Item("COST")
            If CInt(newItemsFromQuickBooks(qbCnt).Item("QNTY_IN_STOCK")) > 0 Then
                newRow.Item("status") = "Enabled"
                newRow.Item("is_in_stock") = "1"
            Else
                If CheckBoxDisableZeroStock.Checked Then
                    newRow.Item("status") = "Disabled"
                End If
            End If
            newRow.Item("qty") = newItemsFromQuickBooks(qbCnt).Item("QNTY_IN_STOCK")
            newRow.Item("visibility") = "Catalog, Search"
            newRow.Item("tax_class_id") = "Taxable Goods"
            newRow.Item("attribute_set") = qbAttrib.GetAttribute(newItemsFromQuickBooks(qbCnt).Item("NAME").ToString)

            newRow.Item("short_description") = newItemsFromQuickBooks(qbCnt).Item("DESC")
            newRow.Item("description") = newItemsFromQuickBooks(qbCnt).Item("DESC")

            newRow.Item("weight") = "0"



            If CheckBoxSetNewCategory.Checked Then
                newRow.Item("category_ids") = NumericNewCategoryNumber.Value.ToString
            End If
            convertedNewItemsFromQuickBooks.Rows.Add(newRow)

            qbCnt += 1
        Loop
        convertedNewItemsFromQuickBooks.AcceptChanges()

        SkippedDataGridView.DataSource = newItemsFromQuickBooks





        '------------ all operations complete - update GUI
        NewDataGridView.DataSource = convertedNewItemsFromQuickBooks
        LostMagentoSKUDataGridView.DataSource = lostMagentoSKU

        TabLostMagentoSKU.Text = "Lost Mgento (" & lostMagentoSKU.Rows.Count.ToString & ")"
        TabNew.Text = "New (" & convertedNewItemsFromQuickBooks.Rows.Count.ToString & ")"

        TabUpdated.Text = "Updated (" & updatedTable.Rows.Count.ToString & ")"
        UpdatedDataGridView.DataSource = updatedTable
    End Sub

    Public Sub removeUnwantedItems()
        skippedTable = quickbooksTable.Copy
        quickbooksTable.Clear()
        'put SKU into the first columne -easier to work with - and make name the item category
        Dim cnt As Integer = 0
        Do While cnt < skippedTable.Rows.Count
            If skippedTable.Rows(cnt).Item("NAME").ToString.Contains(":") And skippedTable.Rows(cnt).Item("INVITEMTYPE").ToString.Equals("INVENTORY") And Not skippedTable.Rows(cnt).Item("SALESTAXCODE").ToString.Equals("S") And Not skippedTable.Rows(cnt).Item("DESC").ToString.Equals("") And Not skippedTable.Rows(cnt).Item("PURCHASEDESC").ToString.Equals("") Then

                skippedTable.Rows(cnt).Item("!INVITEM") = skippedTable.Rows(cnt).Item("NAME").ToString.Remove(0, skippedTable.Rows(cnt).Item(1).ToString.LastIndexOf(CChar(":")) + 1)
                quickbooksTable.ImportRow(skippedTable.Rows(cnt))

                skippedTable.Rows(cnt).Item("!INVITEM") = "OK"
            Else
                skippedTable.Rows(cnt).Item("!INVITEM") = "X"
            End If

            cnt += 1

            progCount += 1
            If progCount > 100 Then
                loadQB.Text = "Sorting Data -%" & CStr(CInt(cnt / skippedTable.Rows.Count) * 100)
                loadQB.Update()
                progCount = 0
            End If
        Loop
        SkippedDataGridView.DataSource = skippedTable
        TabSkipped.Text = "Skipped (" & skippedTable.Rows.Count.ToString & ")"
        TabQuickbooksDB.Text = "QuickBooks DB (" & quickbooksTable.Rows.Count.ToString & ")"
    End Sub

    Private Sub btnSaveMergedAndUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveMergedAndUpload.Click

        Dim sFDlg As New SaveFileDialog

        updatedTable.AcceptChanges()

        sFDlg.Filter = "Magento Excel XML|*.xml"
        sFDlg.Title = "Save Magento Data File..."
        sFDlg.FileName = "merged.xml"
        sFDlg.InitialDirectory = My.Settings.local_workPath

        If sFDlg.ShowDialog = Windows.Forms.DialogResult.OK Then

            Dim xml_saver As New xml_loader
            xml_saver.save_xml(sFDlg.FileName, updatedTable, btnSaveMergedAndUpload) 'no filename specified-will open save file dialog -if specified then overwrite and no promtp!


            Dim uploader As New FtpConnector
            Dim loader As New OpenFileDialog


            If My.Computer.FileSystem.FileExists(sFDlg.FileName) Then

                'MessageBox.Show(My.Settings.ftp_site & "/htdocs/" & sFDlg.FileName)
                uploader.upload(sFDlg.FileName, My.Settings.ftp_site, My.Settings.ftp_import_dir, "desc_import.xml")
            End If


            Dim profiler As New webAdmin

            profiler.adminURL = My.Settings.www_site & My.Settings.www_run & My.Settings.www_id_importXML
            profiler.ShowDialog()
        End If

    End Sub

    Private Sub btnSaveNewAndUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveNewAndUpload.Click
        Dim sFDlg As New SaveFileDialog

        updatedTable.AcceptChanges()

        sFDlg.Filter = "Magento Excel XML|*.xml"
        sFDlg.Title = "Save Magento Data File..."
        sFDlg.FileName = "newItems.xml"
        sFDlg.InitialDirectory = My.Settings.local_workPath

        If sFDlg.ShowDialog = Windows.Forms.DialogResult.OK Then

            Dim xml_saver As New xml_loader
            xml_saver.save_xml(sFDlg.FileName, convertedNewItemsFromQuickBooks, btnSaveNewAndUpload) 'no filename specified-will open save file dialog -if specified then overwrite and no promtp!


            Dim uploader As New FtpConnector
            Dim loader As New OpenFileDialog


            If My.Computer.FileSystem.FileExists(sFDlg.FileName) Then
                uploader.upload(sFDlg.FileName, My.Settings.ftp_site, My.Settings.ftp_import_dir, "desc_import.xml")
            End If


            Dim profiler As New webAdmin

            profiler.adminURL = My.Settings.www_site & My.Settings.www_run & My.Settings.www_id_importXML
            profiler.ShowDialog()
        End If

    End Sub

    Private Sub btnExportAndDownload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportAndDownload.Click
        Dim profiler As New webAdmin
        'profiler.adminURL = "http://www.tecnikk.co.uk/index.php/admin/system_convert_gui/run/id/8/"
        profiler.adminURL = My.Settings.www_site & My.Settings.www_run & 1

        If profiler.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim downloader As New FtpConnector
            Dim saveDialog As New SaveFileDialog

            downloader.download(My.Settings.local_workPath, "export4QB.xml", My.Settings.ftp_site, My.Settings.ftp_export_dir, "exportForUpdate.xml")

        End If
    End Sub

End Class